SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[usp_ShowApplyingStudentRecordsCompanyQuery]

	(
		@param_position NVarChar(50),
		@param_name NVarChar(50),
		@param_state NVarChar(50),
	@param_UserID NVARCHAR(50)
	)

AS
	/* SET NOCOUNT ON */
	Declare @Flag2 BIT
	Set @Flag2='False'
	IF @param_state='无'
	begin
	Set @Flag2='True'
	Set @param_state='True'
	end


	SELECT dbo.JOL_Recruitment_ApplyingRecords.id,position,applicant_type,result,JOL_Recruitment.topic,FS_Student.fdStudName,FS_Student.fdStudSchool,FS_Student.fdStudSpec,FS_Student.fdLongevity,FS_Student.fdStudPhone,FS_Student.fdStudHomeTel
	FROM dbo.JOL_Recruitment_ApplyingRecords,dbo.JOL_Recruitment_Position,dbo.JOL_Recruitment_Participants_apply,dbo.JOL_Employer,FS_Student,JOL_Recruitment
	WHERE
	
	 dbo.JOL_Recruitment_ApplyingRecords.recruit_position=dbo.JOL_Recruitment_Position.id AND
	 participants_apply=dbo.JOL_Recruitment_Participants_apply.id AND
	 employing_unit=JOL_Employer_No AND 
	 JOL_Employer_Account=@param_UserID AND 
	 dbo.JOL_Recruitment_ApplyingRecords.applicant_type='Student'AND
	  dbo.JOL_Recruitment_Participants_apply.recruitment=JOL_Recruitment.id AND 
	  dbo.JOL_Recruitment_ApplyingRecords.applicant_userID=FS_Student.fdStudNo AND (dbo.JOL_Recruitment_ApplyingRecords.result=@param_state OR @Flag2='true') AND dbo.JOL_Recruitment_Position.position LIKE '%'+@param_position+'%' AND FS_Student.fdStudName LIKE '%'+@param_name+'%' 
	ORDER BY dbo.JOL_Recruitment_ApplyingRecords.result,
	dbo.JOL_Recruitment_ApplyingRecords.update_time,dbo.JOL_Recruitment_ApplyingRecords.add_time DESC
	
	RETURN
GO
